﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.IO;
using System.Xml;
using System.Data.SqlClient;

namespace Server
{
    /// <summary>
    /// Summary description for Service3
    /// </summary>
    [WebService(Namespace = "http://localhost:6442/se137/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    [System.Web.Script.Services.ScriptService]
    public class Service3 : System.Web.Services.WebService
    {

        

        private static string connectionString = Conn.connectionString;
        private static int numbering = 1;

        [WebMethod]
        public int AddReview(string username, string bId, string text_excerpt, int rating)
        {

            // Open DB Connection
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();

            //set up a transaction, consists of inserting the user and the review
            SqlTransaction mySqlTransaction = conn.BeginTransaction();

            try
            {

                SqlCommand cmd = conn.CreateCommand();

                //set transaction status
                cmd.Transaction = mySqlTransaction;

                //1st statement, insert the username into the users table
                cmd.CommandText = "INSERT INTO USERS (username) VALUES ('" + username + "')";
                int result = cmd.ExecuteNonQuery();
                
                cmd.CommandText = "SELECT MAX(userId) AS 'UID' FROM USERS";
                SqlDataReader rdr2 = cmd.ExecuteReader();

                rdr2.Read();
                //retrieve new user id
                string newId = rdr2.GetValue(rdr2.GetOrdinal("UID")).ToString();
                string revId = newId + numbering++;
                //insert the review
                string date = DateTime.Today.Year + "-" + DateTime.Today.Month + "-" + DateTime.Today.Day;
                cmd.CommandText = "INSERT INTO REVIEWS( REVID, BID, text_excerpt, userId, rating, date) VALUES ( '" +
                    revId + "', " + "'"
                    + bId + "', " + "'"
                    + text_excerpt + "', "
                    + newId + ", "
                    + rating + ", "
                    + "'" + date + "'" + ")";
                rdr2.Close();
                int res2 = cmd.ExecuteNonQuery();
                mySqlTransaction.Commit();
                return 0;               

            }
            catch (Exception e)
            {
                mySqlTransaction.Rollback();
                return 1;
            }
        }
    }
}

